--------------------------------------------------------
--  File created - Friday-April-19-2013   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Package Body TABLE1_MANAGE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "SIXLABS"."TABLE1_MANAGE" AS

  function count_type(typef in table1.type%type) return integer AS
    rowcount integer;
  BEGIN
    select count(table1.id) into rowcount from table1 where table1.type = typef;
    RETURN rowcount;
  END count_type;

  function count_type_pref(typef in table1.type%type, preff in table1.pref%type) return integer AS
    rowcount integer;
  BEGIN
    select count(table1.id) into rowcount from table1 where table1.type = typef and table1.pref = preff;    
  RETURN rowcount;
  END count_type_pref;

  function create_row
  (typef in table1.type%type, 
  preff in table1.pref%type, 
  amountf in table1.amount%type) 
  return table1.id%type AS
    idf table1.id%type;
  BEGIN
    select table1_seq.nextval into idf from dual;
    insert into table1 (table1.id, table1.type, table1.pref, table1.amount) values (idf, typef, preff, amountf);
    RETURN idf;
  END create_row;

  procedure drop_row(idf in table1.id%type) AS
    rowcount integer;
  BEGIN
    select count(table1.id) into rowcount from table1 where table1.id = idf;
    if(rowcount > 0) then
      delete from table1 where table1.id = idf;
    else
      raise_application_error( -20017, 'SIXLABS ERROR: TABLE1 row delete: '||idf||' does not exist' );
    end if;  
  END drop_row;

  procedure update_row(idf in table1.id%type, amountf in table1.amount%type) AS
    rowcount integer;
  BEGIN
    select count(table1.id) into rowcount from table1 where table1.id = idf;
    if(rowcount > 0) then
      update table1 set table1.amount = amountf where table1.id = idf;
    else
      raise_application_error( -20018, 'SIXLABS ERROR: TABLE1 row update: '||idf||' does not exist' );
    end if;  
  END update_row;

END TABLE1_MANAGE;

/

